import os
import sys

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import dim_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import *
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def device_iot(event):
    date = event.get('p_date')
    if not date:
        date = (datetime.datetime.today()).strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    date = (datetime.datetime.strptime(date,'%Y-%m-%d') - datetime.timedelta(days=1)).strftime('%Y-%m-%d')
    # 查询现有日期
    p_dates = []
    for d in submit("select p_date from dim.device_iot group by p_date")['result']:
        p_dates.append(d[0])
    # 最近日期
    latest_date = max(p_dates)
    print_to_log("最近日期s：" + str(p_dates))
    if date == latest_date or len(p_dates) == 0:
        print_to_log("没有操作，退出")
        output = {"msg": f"{table_name} 没有操作，因为当前日期已经有数据，或者没有初始化表数据", "Payload": {}}
        output = json.dumps(output)
        step_response = send_task_success(tasktoken, output)
        print_to_log("step_response: ", step_response)
        return
    sql = f'''insert into dim.device_iot
                (
                    select
                        if(a.sn is not null,a.sn,b.sn) as sn,
                        if(a.sn is not null,a.model,b.model) as model,
                        if(a.sn is not null,a.material,b.material) as material,
                        'FRIDGE',
                        if(b.sn is not null,b.starttime,a.starttime) as starttime,
                        if(a.sn is not null,a.lasttime,b.lasttime) as lasttime,
                        if(b.sn is not null,b.firsttime,a.firsttime) as firsttime,
                        if(a.sn is not null,a.report_days,0) + if(b.sn is not null,b.report_days,0) as report_days,
                        if(a.sn is not null,a.report_times,0) + if(b.sn is not null,b.report_times,0) as report_times,
                        if(a.sn is not null,a.province,b.province) as province,
                        if(a.sn is not null,a.provinceid,b.provinceid) as provinceid,
                        if(a.sn is not null,a.city,b.city) as city,
                        if(a.sn is not null,a.cityid,b.cityid) as cityid,
                        if(a.sn is not null,a.district,b.district) as district,
                        if(a.sn is not null,a.districtid,b.districtid) as districtid,
                        if(b.sn is not null,b.factory_date,a.factory_date) as factory_date,
                        if(b.sn is not null,a.factory_addr,a.factory_addr) as factory_addr,
                        '{date}'
                    from
                    (
                        select
                            t1.sn,t1.model,t1.material,'FRIDGE',t1.starttime,t1.lasttime lasttime,t1.firsttime,
                            t1.report_days,t1.report_times,
                            concat(a1.province_name,a1.province_suffix) as province,
                            concat(a1.province_code,'0000') as provinceid,
                            t2.city,
                            t2.cityid,
                            d2.district,
                            if(d2.districtid is null,cast(t1.city as varchar),d2.districtid) as districtid,
                            concat(
                                    t3.name,'-',
                                    substr(t1.sn,17,2),'-',
                                    substr(t1.sn,19,2)
                            ) factory_date,
                            t4.name as factory_addr
                        from
                        (
                             select
                                sn,max(model) as model,max(material) as material,max(city) city,min(starttime) starttime,max(timestamp) lasttime,min(timestamp) as firsttime,
                                count(distinct p_date) as report_days,
                                count(1) as report_times
                            from
                                ods.iot_fridge
                            where
                                p_date > '{latest_date}'
                            group by
                                sn
                        ) t1
                        left join
                            dim.area_data a1
                        on
                            substr(cast(t1.city as varchar),1,2) = substr(a1.province_code,1,2)
                        left join
                            dim.cities t2
                        on
                            substr(cast(t1.city as varchar),1,4) = substr(t2.cityid,1,4)
                        left join
                            dim.district d2
                        on
                            cast(t1.city as varchar) = d2.districtid
                        left join
                            dim.tree_book t3
                        on
                            substr(t1.sn,16,1) = t3.code and t3.classify_code = '0'
                        left join
                            dim.tree_book t4
                        on
                            substr(t1.sn,1,1) = t4.code and t4.classify_code = '1'
                    ) a
                    full join
                    (
                        select
                            *
                        from
                            dim.device_iot
                        where
                            p_date = '{latest_date}'
                    ) b
                    on
                        a.sn = b.sn
                )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    # 删除10天前分区和数据
    if len(p_dates) > 10:
        delete_date = min(p_dates)
        print_to_log("删除分区：" + delete_date)
        athena_response2 = submit(f"alter table dim.device_iot drop partition(p_date = '{delete_date}')")
        print_to_log("athena_response: ", athena_response2)
        delete(dim_bucket, f'{table_name}/p_date={delete_date}')

    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        device_iot(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)
    # device_iot({})
